package com.ph.irp.sell.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.model.PSellSellpricedetail;
import com.ph.irp.sell.dao.SellPriceDetailDao;
import com.ph.util.DBUtil;

public class SellPriceDetailDaoImpl implements SellPriceDetailDao{

	
	/** The data source. */
	private ComboPooledDataSource dataSource;

	public ComboPooledDataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	
	
	public void addSellPriceDetail(PSellSellpricedetail sellpricedetail)throws Throwable{
		
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			con = dataSource.getConnection();
			pStmt = con
					.prepareStatement("insert into P_sell_sellpricedetail (materialId,sellPriceId,amountEnd,amountStart,baseUnitNoEnd,baseUnitNoStart,addTaxUnitPrice,baseUnitName,discountRate,productCode,productName,realAddTaxUnitPrice,remark,standardType,taxRate,unit,unitPrice) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
			pStmt.setInt(1, sellpricedetail.getMaterialId());
			pStmt.setInt(2, sellpricedetail.getSellPriceId());
			pStmt.setFloat(3, sellpricedetail.getAmountEnd());
			pStmt.setFloat(4, sellpricedetail.getAmountStart());
			pStmt.setFloat(5, sellpricedetail.getBaseUnitNoEnd());
			pStmt.setFloat(6, sellpricedetail.getBaseUnitNoStart());
			pStmt.setFloat(7, sellpricedetail.getAddTaxUnitPrice());
			pStmt.setString(8, sellpricedetail.getBaseUnitName());
			pStmt.setFloat(9, sellpricedetail.getDiscountRate());
			pStmt.setString(10, sellpricedetail.getProductCode());
			pStmt.setString(11, sellpricedetail.getProductName());
			pStmt.setFloat(12, sellpricedetail.getRealAddTaxUnitPrice());
			pStmt.setString(13, sellpricedetail.getRemark());
			pStmt.setString(14, sellpricedetail.getStandardType());
			pStmt.setFloat(15, sellpricedetail.getTaxRate());
			pStmt.setString(16, sellpricedetail.getUnit());
			pStmt.setFloat(17, sellpricedetail.getUnitPrice());
			
			pStmt.executeUpdate();
		} catch (SQLException e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
	}
	
	
	public List<PSellSellpricedetail> getSellPriceDetailBySellPriceId(int sellPriceId)throws Throwable{
	
		List<PSellSellpricedetail> sellPriceList = new ArrayList<PSellSellpricedetail>();

		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;

		try {
			// 由数据库获得连接
			con = this.dataSource.getConnection();

			statement = con
					.prepareStatement("select * from p_sell_sellpricedetail where sellPriceId=?");
			// 给宿主变量绑定值
			statement.setInt(1, sellPriceId);
			// 执行预备语句，得到结果集
			resultSet = statement.executeQuery();

			turnResultSetToList(sellPriceList, resultSet);

		} catch (Exception e) {// 捕捉异常并进行处理
			// TODO Auto-generated catch block
			throw new Exception(e.getMessage());
		} finally {// 关闭数据库连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
		return sellPriceList;
	}
	/***
	 * 删除SellPriceDetail
	 */
	public void deleteSellPriceDetail(int sellPriceId)throws Throwable{
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			con = dataSource.getConnection();
			pStmt = con
					.prepareStatement("delete from p_sell_sellpricedetail where sellPriceId = ?");
			pStmt.setInt(1, sellPriceId);

			pStmt.executeUpdate();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}

	}
	/**
	 * 迭代结果集，将数据封装到sellPriceList中
	 * 
	 * @param settlementList
	 * @param resultSet
	 * @return
	 * @throws Throwable
	 */
	public List<PSellSellpricedetail> turnResultSetToList(
			List<PSellSellpricedetail> sellPriceDetailList, ResultSet resultSet)
			throws Throwable {

		// 迭代结果集
		while (resultSet.next()) {

			PSellSellpricedetail sellPriceDetail = new PSellSellpricedetail();

			sellPriceDetail.setAddTaxUnitPrice(resultSet.getFloat("addTaxUnitPrice"));
			sellPriceDetail.setAmountEnd(resultSet.getFloat("amountEnd"));
			sellPriceDetail.setAmountStart(resultSet.getFloat("amountStart"));
			sellPriceDetail.setBaseUnitNoEnd(resultSet.getFloat("baseUnitNoEnd"));
			sellPriceDetail.setBaseUnitName(resultSet.getString("baseUnitName"));
			sellPriceDetail.setBaseUnitNoStart(resultSet.getFloat("baseUnitNoStart"));
			sellPriceDetail.setDiscountRate(resultSet.getFloat("discountRate"));
			sellPriceDetail.setMaterialId(resultSet.getInt("materialId"));
			sellPriceDetail.setProductCode(resultSet.getString("productCode"));
			sellPriceDetail.setProductName(resultSet.getString("productName"));
			sellPriceDetail.setRealAddTaxUnitPrice(resultSet.getFloat("realAddTaxUnitPrice"));
			sellPriceDetail.setRemark(resultSet.getString("remark"));
			sellPriceDetail.setSellPriceDetailId(resultSet.getInt("sellPriceDetailId"));
			sellPriceDetail.setSellPriceId(resultSet.getInt("sellPriceId"));
			sellPriceDetail.setStandardType(resultSet.getString("standardType"));
			sellPriceDetail.setTaxRate(resultSet.getFloat("taxRate"));
			sellPriceDetail.setUnit(resultSet.getString("unit"));
			sellPriceDetail.setUnitPrice(resultSet.getFloat("unitPrice"));
	
			sellPriceDetailList.add(sellPriceDetail);
		}

		return sellPriceDetailList;
	}
	
}
